﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;

namespace UbbTimetable2
{
    /// <summary>
    /// A struct to store professor data.
    /// </summary>
    public struct Prof
    {
        int _cnp;
        int _rankId;
        String _rank;
        String _fName;
        String _lName;
        String _fullName;
        String _tel;
        String _email;
        int _hours;

        public int Hours
        {
            get { return _hours; }
            set { _hours = value; }
        }

        public int Cnp
        {
            get { return _cnp; }
            set { _cnp = value; }
        }

        public int RankId
        {
            get { return _rankId; }
            set { _rankId = value; }
        }

        public String Rank
        {
            get { return _rank; }
            set { _rank = value; }
        }

        public String Fname
        {
            get { return _fName; }
            set { _fName = value; }
        }

        public String Lname
        {
            get { return _lName; }
            set { _lName = value; }
        }

        public String FullName
        {
            get { return _fullName; }
            set { _fullName = value; }
        }

        public String Tel
        {
            get { return _tel; }
            set { _tel = value; }
        }

        public String Email
        {
            get { return _email; }
            set { _email = value; }
        }
    }

    /// <summary>
    /// A struct to store professor rank data.
    /// </summary>
    public struct Rank
    {
        int _rankId;

        public int RankId
        {
            get { return _rankId; }
            set { _rankId = value; }
        }
        String _rankName;

        public String RankName
        {
            get { return _rankName; }
            set { _rankName = value; }
        }
    }

    /// <summary>
    /// A DAO that handles all data related to professors.
    /// </summary>
    public class ProfDao : Dao
    {
        private String err = "";

        /// <summary>
        /// The last error message.
        /// </summary>
        public String Err
        {
            get { return err; }
        }

        /// <summary>
        /// Returns the list of professors.
        /// </summary>
        /// <returns>The list of professors.</returns>
        public List<Prof> GetProfList()
        {
            String query = "SELECT * FROM Tanar AS T, Szemely AS Sz, Beosztas AS B";
            query += " WHERE T.SzemelyID = Sz.CNP AND T.BeosztasID = B.ID";

            SqlDataReader rdr = ExecuteReader(query, ref err);

            List<Prof> list = new List<Prof>();
            if (err == "OK")
            {
                while (rdr.Read())
                {
                    Prof item = new Prof();
                    item.Cnp = Convert.ToInt32(rdr[0]);
                    item.RankId = Convert.ToInt32(rdr[1]);
                    item.Hours = Convert.ToInt32(rdr[2]);
                    item.Lname = rdr[4].ToString().TrimEnd();
                    item.Fname = rdr[5].ToString().TrimEnd();

                    item.Tel = rdr[6].ToString().TrimEnd();
                    item.Email = rdr[7].ToString().TrimEnd();
                    item.Rank = rdr[9].ToString().TrimEnd();
                    item.FullName = item.Rank + " " + item.Lname + " " + item.Fname;

                    list.Add(item);
                }
            }
            CloseDataReader(rdr);
            return list;
        }

        /// <summary>
        /// Returns the list of ranks.
        /// </summary>
        /// <returns>The list of ranks.</returns>
        public List<Rank> GetRankList()
        {
            String query = "SELECT ID, Nev FROM Beosztas";

            SqlDataReader rdr = ExecuteReader(query, ref err);
            List<Rank> list = new List<Rank>();
            if (err == "OK")
            {
                while (rdr.Read())
                {
                    Rank item = new Rank();
                    item.RankId = Convert.ToInt32(rdr[0]);
                    item.RankName = rdr[1].ToString().TrimEnd();

                    list.Add(item);
                }
            }
            CloseDataReader(rdr);
            return list;
        }

        /// <summary>
        /// Inserts a new professor.
        /// </summary>
        /// <param name="cnp">The personal identification number.</param>
        /// <param name="lname">The last name.</param>
        /// <param name="fname">The first name.</param>
        /// <param name="tel">The telephone number.</param>
        /// <param name="email">The email address.</param>
        /// <param name="rankId">The id of it's rank.</param>
        public void InsertProf(String cnp, String lname, String fname, String tel, String email, int rankId)
        {
            string[] parameterNames = new string[6], parameterValues = new string[6];
            parameterNames[0] = "@cnp"; parameterValues[0] = cnp;
            parameterNames[1] = "@vezNev"; parameterValues[1] = lname;
            parameterNames[2] = "@kerNev"; parameterValues[2] = fname;
            parameterNames[3] = "@telSzam"; parameterValues[3] = tel;
            parameterNames[4] = "@email"; parameterValues[4] = email;
            parameterNames[5] = "@beosztId"; parameterValues[5] = rankId.ToString();

            err = ExecuteStoredProcedureNonQuery("UjTanar", parameterNames, parameterValues);
        }

        /// <summary>
        /// Updates a professors data..
        /// </summary>
        /// <param name="cnp">The personal identification number.</param>
        /// <param name="lname">The last name.</param>
        /// <param name="fname">The first name.</param>
        /// <param name="tel">The telephone number.</param>
        /// <param name="email">The email address.</param>
        /// <param name="rankId">The id of it's rank.</param>
        public void UpdateProf(String cnp, String lname, String fname, String tel, String email, int rankId)
        {
            string[] parameterNames = new string[6], parameterValues = new string[6];
            parameterNames[0] = "@cnp"; parameterValues[0] = cnp;
            parameterNames[1] = "@vezNev"; parameterValues[1] = lname;
            parameterNames[2] = "@kerNev"; parameterValues[2] = fname;
            parameterNames[3] = "@telSzam"; parameterValues[3] = tel;
            parameterNames[4] = "@email"; parameterValues[4] = email;
            parameterNames[5] = "@beosztId"; parameterValues[5] = rankId.ToString();

            err = ExecuteStoredProcedureNonQuery("FrissitTanar", parameterNames, parameterValues);
        }

        /// <summary>
        /// Deletes a professor.
        /// </summary>
        /// <param name="cnp">The personal identification number.</param>
        public void DeleteProf(String cnp)
        {
            string[] parameterNames = new string[1], parameterValues = new string[1];
            parameterNames[0] = "@cnp"; parameterValues[0] = cnp;

            err = ExecuteStoredProcedureNonQuery("TorolTanar", parameterNames, parameterValues);
        }
    }
}
